const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *销退接口
 */

//  销退开始时间
// let startTime = '2019-05-17 00:00:00';
let startTime = '';
//  获得订单信息

const getReturnGoodsOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,J.XH,Z.RQ,Z.ID,Z.DWBM,Z.BMBM,Z.BMMC,Z.DWMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.JBRMC,Z.SKQX,Z.SKQZ,Z.PJLX,Z.PJHM,Z.PZH,Z.ZJS,Z.ZSS,Z.ZHJ,Z.ID_DHD,J.BM,J.MC,J.KM1BM,J.KMBM,J.KM2BM,J.KM3BM,J.KMQC,J.LX,B.DH FROM XSD_ZB Z LEFT JOIN XSD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM LEFT JOIN (SELECT SUM(SL) AS SL,ID FROM XSD_MX GROUP BY ID) M ON Z.ID  = M.ID LEFT JOIN (SELECT SUM(RM.SL) AS BJSL,RZ.ID_XSD FROM XTD_MX RM LEFT JOIN XTD_ZB RZ ON RM.ID = RZ.ID GROUP BY RZ.ID_XSD ) T1 ON Z.ID = T1.ID_XSD AND M.SL<=T1.BJSL  WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND Z.RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}' AND T1.ID_XSD IS NULL  `;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL FROM XSD_ZB Z LEFT JOIN XSD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM LEFT JOIN (SELECT SUM(SL) AS SL,ID FROM XSD_MX GROUP BY ID) M ON Z.ID  = M.ID LEFT JOIN (SELECT SUM(RM.SL) AS BJSL,RZ.ID_XSD FROM XTD_MX RM LEFT JOIN XTD_ZB RZ ON RM.ID = RZ.ID GROUP BY RZ.ID_XSD ) T1 ON Z.ID = T1.ID_XSD AND M.SL<=T1.BJSL  WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND Z.RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}' AND T1.ID_XSD IS NULL `
    if (body.searchVal.DWMC) {
        startSql += ` AND (B.MC LIKE '%${body.searchVal.DWMC}%' OR B.DH LIKE '%${body.searchVal.DWMC}%')`;
        totalSql += ` AND (B.MC LIKE '%${body.searchVal.DWMC}%' OR B.DH LIKE '%${body.searchVal.DWMC}%')`;
    }
    if (body.searchVal.ID) {
        startSql += ` AND Z.ID = '${body.searchVal.ID}' `;
        totalSql += ` AND Z.ID = '${body.searchVal.ID}' `;
    }
    if (body.searchVal.ID_DHD) {
        startSql += ` AND Z.ID_DHD = '${body.searchVal.ID_DHD}' `;
        totalSql += ` AND Z.ID_DHD = '${body.searchVal.ID_DHD}' `;
    }
    if (body.searchVal.YPMC) {
        startSql += ` AND Z.ID IN (SELECT M.ID FROM XSD_MX M LEFT JOIN BM_YP Y ON M.YPBM = Y.BM WHERE M.YPBM  LIKE '%${body.searchVal.YPMC}%' OR M.YPTM  LIKE '%${body.searchVal.YPMC}%' OR M.YPMC  LIKE '%${body.searchVal.YPMC}%' OR Y.JP  LIKE '%${body.searchVal.YPMC}%') `;
        totalSql += ` AND Z.ID IN (SELECT M.ID FROM XSD_MX M LEFT JOIN BM_YP Y ON M.YPBM = Y.BM WHERE M.YPBM  LIKE '%${body.searchVal.YPMC}%' OR M.YPTM  LIKE '%${body.searchVal.YPMC}%' OR M.YPMC  LIKE '%${body.searchVal.YPMC}%' OR Y.JP  LIKE '%${body.searchVal.YPMC}%') `;
    }
    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    startSql += endSql;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}


//  获得订单对应的商品信息
const getReturnShop = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT M.YPBM,M.YPMC,M.SCPH,M.YPGG,M.JLDW,M.JXMC,M.SCRQ,M.YXQZ,M.SCCJ,M.PZWH,Y.YPLX,M.CBJJ,Y.CCTJ,M.DJ,M.YPTM,
        M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,Y.JG_FLAG,Y.YXFL,Y.JP,SUM(M.SL) AS SL,SUM(M.JE) AS JE,SUM(Y.MJSL) AS MJSL,SUM(M.JS) AS JS,SUM(M.SS) AS SS,
        SUM(M.HJ) AS HJ,Y.SSXK FROM XSD_MX M LEFT JOIN BM_YP Y ON M.YPBM =  Y.BM   WHERE M.ID = '${body.orderId}' GROUP BY M.YPBM,M.YPMC,M.SCPH,M.YPGG,
        M.JLDW,M.JXMC,M.SCRQ,M.YXQZ,M.SCCJ,M.PZWH,Y.YPLX,M.CBJJ,Y.CCTJ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,Y.JG_FLAG,Y.YXFL,Y.JP,Y.SSXK) t1 
        LEFT JOIN (SELECT * FROM (SELECT R.YPBM AS BJYPBM,R.SCPH AS BJSCPH,SUM(R.SL) AS BJSL FROM XTD_MX R LEFT JOIN XTD_ZB Z ON R.ID = Z.ID 
        WHERE Z.ID_XSD = '${body.orderId}' GROUP BY R.YPBM,R.SCPH ) t3 WHERE t3.BJYPBM IS NOT NULL AND T3.BJSCPH IS NOT NULL) t2 ON t2.BJYPBM = 
        t1.YPBM AND T2.BJSCPH = T1.SCPH AND t2.BJSL < t1.SL WHERE t2.BJSL IS NOT NULL OR t1.YPBM <>ALL (SELECT R.YPBM FROM XTD_MX R LEFT JOIN  
        XTD_ZB Z ON R.ID = Z.ID WHERE Z.ID_XSD = '${body.orderId}')`;
    let result = await sql.query(startSql);
    ctx.body = {
        data: result.recordset
    };
}


// 补全时间
const repairTime = (time) => {
    return time.toString().length == 1 ? ('0' + time) : time;
}
// 获取时间拼接的年月日
const getNowTimeId = (time) => {
    let nowTime = time ? new Date(time) : new Date();
    let y = nowTime.getFullYear();
    let M = nowTime.getMonth() + 1;
    let d = nowTime.getDate();
    let h = nowTime.getHours();
    let m = nowTime.getMinutes();
    let s = nowTime.getSeconds();
    return {
        msDate: repairTime(m) + repairTime(s),
        date: y.toString().slice(2) + repairTime(M) + repairTime(d),
        fullDate: `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(h)}:${repairTime(m)}:${repairTime(s)}.000`
    }
}
// 获得退货人名称
const getJBRMC = (val) => {
    let storageList = [
        {
            label: "余梅",
            value: "0715"
        },
        {
            label: "屠博州",
            value: "0717"
        }, {
            label: "余春柳",
            value: "0729"
        },
        {
            label: "刘颜",
            value: "0730"
        },
        {
            label: "杨正欣",
            value: "0212"
        },
        {
            label: "吕丹",
            value: "0731"
        }
    ]
    return storageList.filter(item => item.value == val)[0].label;
}
//  保存订单信息
const keepReturnInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let lastOrderSql = `SELECT TOP 1 ID FROM XTD_ZB WHERE ID LIKE '${getNowTimeId().date}071413%' ORDER BY ID DESC`;
    let oldOrder = await sql.query(lastOrderSql);
    let newSerialNum = 1;
    if (oldOrder.recordset.length !== 0) {
        let oldOrderId = oldOrder.recordset[0].ID;
        let oldSerialNum = oldOrderId.slice(oldOrderId.length - 4);
        newSerialNum = Number(oldSerialNum) + 1;
    }
    newSerialNum = newSerialNum.toString();
    newSerialNum = '0000'.slice(0, 4 - newSerialNum.length) + newSerialNum;
    let newId = getNowTimeId().date + '07' + '14' + '13' + newSerialNum;
    let shopInfor = body.entryList;
    let insertMxSql = `INSERT INTO XTD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,YXQZ,JLDW,MJSL,XS,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,JE,RT,SE,HJ,ZKJE,CBJJ,XSCB,XSML,CKBM,CKMC,HWBH,JFGZ,ZP,DZ,JF,CB,JG,UID,YSRQ,BZQK,ZLZK,YSJL,YSR2,XSSL) `;

    let zje = 0, zss = 0, zjs = 0, zsl = 0, zcb = 0, zml = 0;

    shopInfor.forEach((item, ind) => {
        let dj = Number(item.DJ ? item.DJ : 0);
        let thsl = Number(item.THSL ? item.THSL : 0);
        let mjsl = Number(item.MJSL ? item.MJSL : 0);
        let cbjj = Number(item.CBJJ ? item.CBJJ : 0);
        let je = dj * thsl;
        let js = mjsl === 0 ? 0 : Math.floor(thsl / mjsl);
        let ss = thsl - (mjsl * js);
        let xscb = Number((cbjj * thsl).toFixed(2));
        let xsml = Number(((dj - cbjj) * thsl).toFixed(2));
        zcb += xscb;
        zml += xsml;
        zje += je;
        zss += ss;
        zjs += js;
        zsl += thsl;
        let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);
        if (ind == shopInfor.length - 1) {
            insertMxSql += `SELECT '${newId}',${getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM === null ? '' : item.YPTM}','${item.YPMC}','${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC === null ? '' : item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}','${item.JLDW}',${item.MJSL},${item.SL},${item.THSL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},${item.DJ_HS},${je},0,0,${je},0,${item.CBJJ},${xscb},${xsml},'${item.CCTJ == "常温" ? '0701' : '0702'}','${item.CCTJ}库','${item.HWBH.toUpperCase()}','${item.THYY}','F','T','T','A','F','', '${baseFn.getNowFormatDate()}','${item.BZQK}','${item.ZLZK}','${item.YSJL}','${body.queryList.SHR === "0717" ? "屠博洲" : "余梅"}',${item.SL} `;
        } else {
            insertMxSql += `SELECT '${newId}',${getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM === null ? '' : item.YPTM}','${item.YPMC}','${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC === null ? '' : item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}','${item.JLDW}',${item.MJSL},${item.SL},${item.THSL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},${item.DJ_HS},${je},0,0,${je},0,${item.CBJJ},${xscb},${xsml},'${item.CCTJ == "常温" ? '0701' : '0702'}','${item.CCTJ}库','${item.HWBH.toUpperCase()}','${item.THYY}','F','T','T','A','F','', '${baseFn.getNowFormatDate()}','${item.BZQK}','${item.ZLZK}','${item.YSJL}','${body.queryList.SHR === "0717" ? "屠博洲" : "余梅"}',${item.SL} UNION `;
        }
    });
    let insertJsSql = `INSERT INTO XTD_JS (ID,XH,BM,MC,KM1BM,KM2BM,KM3BM,KMBM,KMQC,DXJF,JSJE,PH,LX) SELECT '${newId}',1,'${body.chooseOrderList.BM}','${body.chooseOrderList.MC}','${body.chooseOrderList.KM1BM}','${body.chooseOrderList.KM2BM}','${body.chooseOrderList.KM3BM}','${body.chooseOrderList.KMBM}','${body.chooseOrderList.KMQC}',NULL,${zje},'',${body.chooseOrderList.LX}`;
    let insertZbSql = `INSERT INTO XTD_ZB (ID,RQ,DWBM,DWMC,BMBM,BMMC,JBRBM,JBRMC,JSFSBM,JSFSMC,ZDR,SHR,PZH,ZDZK,FKQX,FKQZ,PJLX,PJHM,ZSL,ZJS,ZSS,
        ZJE,ZSE,ZHJ,TJE,YJS,XJS,EJS,WJS,ZZK,ZCB,ZML,DXJF,BCJF,CZJE,ZY,BZ,LX,FS,SH,JQH,ID_JBD,ID_XSD) SELECT '${newId}','${getNowTimeId().fullDate}',
        '${body.chooseOrderList.DWBM}','${body.chooseOrderList.DWMC}','${body.chooseOrderList.BMBM}','${body.chooseOrderList.BMMC}',
        '${body.queryList.JBRBM}','${getJBRMC(body.queryList.JBRBM)}','${body.chooseOrderList.JSFSBM ? body.chooseOrderList.JSFSBM : ''}',
        '${body.chooseOrderList.JSFSMC}','${body.queryList.ZDR}','${body.queryList.SHR === "0717" ? "屠博洲" : "余梅"}',NULL,100,
        ${body.chooseOrderList.SKQX},'${body.chooseOrderList.SKQZ}','${body.chooseOrderList.PJLX}',
        '${body.chooseOrderList.PJHM === null ? '' : body.chooseOrderList.PJHM}',${zsl},${zjs},${zss},${zje},0.00,${zje},0.00,${zje},0.00,0.00,
        ${zje},0.00,${zcb},${zml},0,${parseInt(zje / 10)},0,'','${body.queryList.BZ}','零售',1,'F','','','${body.chooseOrderList.ID}'`;
    // let zbResult = await sql.query(insertZbSql);
    // let jsResult = await sql.query(insertJsSql);
    // let mxResult = await sql.query(insertMxSql);
    let lastSql = `SET XACT_ABORT ON Begin Tran ${insertZbSql} ${insertJsSql} ${insertMxSql}  Commit Tran `;
    let result = await sql.query(lastSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }


}

module.exports = {
    'POST /getReturnGoodsOrder': getReturnGoodsOrder,
    'POST /getReturnShop': getReturnShop,
    'POST /keepReturnInfor': keepReturnInfor
}
